import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;

/**
 * @Author YuanYuan
 * @Date 2022/6/15
 * @Time 23:39
 */
//insert into borrow_info (book_id, student_id,start_time, end_time) values (null,select book.id from book
//        where book.name = '诗经',select student.id from student
//        where student.name = '貂蝉','2019-09-25 17:50:00','2019-10-25 17:50:00');
/**1. 新增貂蝉同学的借阅记录：诗经，从2019年9月25日17:50到2019年10月25日17:50
 *
        2. 查询计算机分类下的图书借阅信息
        3. 修改图书《深入理解Java虚拟机》的价格为61.20
        4. 删除id最大的一条借阅记录
 */
public class TestJDBCInsert {
    //日期字符串转Java日期类Date和sql时间戳Timestamp
    public static Timestamp getTimestamp(String dateString){
        try {
            // 年-月-日 小时:分钟:秒
            java.util.Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(dateString);
            return new java.sql.Timestamp(date.getTime());
        } catch (ParseException e) {
            throw new RuntimeException("日期格式化错误："+dateString, e);
        }
    }
    public static void main(String[] args) throws SQLException {

        Scanner scanner = new Scanner(System.in);
        DataSource dataSource = new MysqlDataSource();
        //创建好数据源
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("111111");

        //代码和数据库建立连接
        Connection connection = dataSource.getConnection();

        //3.操作数据库,以插入数据为例:
        //关键所在就是构造一个SQL语句
        //不虚加 ;
//        System.out.println("请输入学号:");
//        int id = scanner.nextInt();
//
//        System.out.println("请输入姓名:");
//        String name = scanner.next();

//        String sql = "insert into borrow_info (book_id, student_id,start_time, end_time) values (null,(select book.id from book" +
//                "        where book.name = ?),(select student.id from student" +
//                "        where student.name = ?),?,?)";
        String sql = "insert into borrow_info(book_id, student_id," +
                " start_time, end_time) select book.id,student.id,?,?" +
                " from book,student where book.name=? and student.name=?";
        //把string包装成一个sql"语句对象";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,"诗经");
        statement.setString(2,"貂蝉");
        statement.setTimestamp(3, getTimestamp("2019-09-25 17:50:00"));
        statement.setTimestamp(4, getTimestamp("2019-10-25 17:50:00"));

        String sql1 = "update book set price=? where name =?";

        statement = connection.prepareStatement(sql1);

        //statement.setBigDecimal(1, new BigDecimal("61.20"));

        statement.setString(2, "深入理解Java虚拟机");
        //执行sql
        System.out.println(statement);
        int ret = statement.executeUpdate();
        System.out.println(ret);

        statement.close();
        connection.close();

    }

}
